#region Using
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using CY.GFive.Core.Business;
using CY.Utility.DBUtility;
#endregion Using

namespace CY.GFive.DALProviders.SqlServerProvider
{
	public class AidedEmployeeProvider : CY.GFive.Core.Providers.DALProvider.IAidedEmployeeProvider
	{
		#region Sql
		private static readonly string SqlSelectAidedEmployee = "SELECT [Id], [EmployeeCode],[PoorLevel],[Name],[People],[Sex],[PolicticalExperience],[Birthdya],[Age],[IDCardNbr],[HealthState],[DisabilityLevel],[Identity],[WorkingModelType],[HouseType],[HouseArea],[PostCode],[Phone],[YearOfWorking],[Industry],[IsMarried],[HUKOUtype],[Address],[Organization],[OZType],[OZState],[IsSingleParent],[IncomingOfMonth],[PersonNbrOfFamily],[HUKOUSite],[IsMedicalInsurance],[Resume],[Amout],[ApplyingDate] FROM [AidedEmployee] WHERE  [Id]=@Id";

		private static readonly string SqlInsertAidedEmployee = "INSERT INTO [AidedEmployee]([EmployeeCode],[PoorLevel],[Name],[People],[Sex],[PolicticalExperience],[Birthdya],[Age],[IDCardNbr],[HealthState],[DisabilityLevel],[Identity],[WorkingModelType],[HouseType],[HouseArea],[PostCode],[Phone],[YearOfWorking],[Industry],[IsMarried],[HUKOUtype],[Address],[Organization],[OZType],[OZState],[IsSingleParent],[IncomingOfMonth],[PersonNbrOfFamily],[HUKOUSite],[IsMedicalInsurance],[Resume],[Amout],[ApplyingDate]) values(@EmployeeCode,@PoorLevel,@Name,@People,@Sex,@PolicticalExperience,@Birthdya,@Age,@IDCardNbr,@HealthState,@DisabilityLevel,@Identity,@WorkingModelType,@HouseType,@HouseArea,@PostCode,@Phone,@YearOfWorking,@Industry,@IsMarried,@HUKOUtype,@Address,@Organization,@OZType,@OZState,@IsSingleParent,@IncomingOfMonth,@PersonNbrOfFamily,@HUKOUSite,@IsMedicalInsurance,@Resume,@Amout,@ApplyingDate);SELECT SCOPE_IDENTITY();";

		private static readonly string SqlUpdateAidedEmployee = "UPDATE [AidedEmployee] SET [EmployeeCode] = @EmployeeCode,[PoorLevel] = @PoorLevel,[Name] = @Name,[People] = @People,[Sex] = @Sex,[PolicticalExperience] = @PolicticalExperience,[Birthdya] = @Birthdya,[Age] = @Age,[IDCardNbr] = @IDCardNbr,[HealthState] = @HealthState,[DisabilityLevel] = @DisabilityLevel,[Identity] = @Identity,[WorkingModelType] = @WorkingModelType,[HouseType] = @HouseType,[HouseArea] = @HouseArea,[PostCode] = @PostCode,[Phone] = @Phone,[YearOfWorking] = @YearOfWorking,[Industry] = @Industry,[IsMarried] = @IsMarried,[HUKOUtype] = @HUKOUtype,[Address] = @Address,[Organization] = @Organization,[OZType] = @OZType,[OZState] = @OZState,[IsSingleParent] = @IsSingleParent,[IncomingOfMonth] = @IncomingOfMonth,[PersonNbrOfFamily] = @PersonNbrOfFamily,[HUKOUSite] = @HUKOUSite,[IsMedicalInsurance] = @IsMedicalInsurance,[Resume] = @Resume,[Amout] = @Amout,[ApplyingDate] = @ApplyingDate WHERE  [Id]=@Id";

		private static readonly string SqlDeleteAidedEmployee = "DELETE [AidedEmployee] WHERE  [Id]=@Id";

		private static readonly string SqlGetAllAidedEmployee = "SELECT [Id], [EmployeeCode],[PoorLevel],[Name],[People],[Sex],[PolicticalExperience],[Birthdya],[Age],[IDCardNbr],[HealthState],[DisabilityLevel],[Identity],[WorkingModelType],[HouseType],[HouseArea],[PostCode],[Phone],[YearOfWorking],[Industry],[IsMarried],[HUKOUtype],[Address],[Organization],[OZType],[OZState],[IsSingleParent],[IncomingOfMonth],[PersonNbrOfFamily],[HUKOUSite],[IsMedicalInsurance],[Resume],[Amout],[ApplyingDate] FROM [AidedEmployee]";
		#endregion

		#region IAidedEmployeeProvider Members
		public Core.Business.AidedEmployee Select(int id)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Id", SqlDbType.Int, id);
			SqlDataReader reader = sql.ExecuteSqlReader(SqlSelectAidedEmployee);

			if (reader != null && !reader.IsClosed && reader.Read())
			{
				Core.Business.AidedEmployee aidedEmployee = new Core.Business.AidedEmployee();

				if (!reader.IsDBNull(0)) aidedEmployee.Id = reader.GetInt32(0);
				if (!reader.IsDBNull(1)) aidedEmployee.EmployeeCode = reader.GetString(1);
				if (!reader.IsDBNull(2)) aidedEmployee.PoorLevel = reader.GetByte(2);
				if (!reader.IsDBNull(3)) aidedEmployee.Name = reader.GetString(3);
				if (!reader.IsDBNull(4)) aidedEmployee.People = reader.GetString(4);
				if (!reader.IsDBNull(5)) aidedEmployee.Sex = reader.GetByte(5);
				if (!reader.IsDBNull(6)) aidedEmployee.PolicticalExperience = reader.GetString(6);
				if (!reader.IsDBNull(7)) aidedEmployee.Birthdya = reader.GetDateTime(7);
				if (!reader.IsDBNull(8)) aidedEmployee.Age = reader.GetByte(8);
				if (!reader.IsDBNull(9)) aidedEmployee.IDCardNbr = reader.GetString(9);
				if (!reader.IsDBNull(10)) aidedEmployee.HealthState = reader.GetByte(10);
				if (!reader.IsDBNull(11)) aidedEmployee.DisabilityLevel = reader.GetByte(11);
				if (!reader.IsDBNull(12)) aidedEmployee.Identity = reader.GetString(12);
				if (!reader.IsDBNull(13)) aidedEmployee.WorkingModelType = reader.GetString(13);
				if (!reader.IsDBNull(14)) aidedEmployee.HouseType = reader.GetString(14);
				if (!reader.IsDBNull(15)) aidedEmployee.HouseArea = reader.GetDecimal(15);
				if (!reader.IsDBNull(16)) aidedEmployee.PostCode = reader.GetInt32(16);
				if (!reader.IsDBNull(17)) aidedEmployee.Phone = reader.GetString(17);
				if (!reader.IsDBNull(18)) aidedEmployee.YearOfWorking = reader.GetByte(18);
				if (!reader.IsDBNull(19)) aidedEmployee.Industry = reader.GetString(19);
				if (!reader.IsDBNull(20)) aidedEmployee.IsMarried = reader.GetByte(20);
				if (!reader.IsDBNull(21)) aidedEmployee.HUKOUtype = reader.GetString(21);
				if (!reader.IsDBNull(22)) aidedEmployee.Address = reader.GetString(22);
				if (!reader.IsDBNull(23)) aidedEmployee.Organization = reader.GetString(23);
				if (!reader.IsDBNull(24)) aidedEmployee.OZType = reader.GetString(24);
				if (!reader.IsDBNull(25)) aidedEmployee.OZState = reader.GetString(25);
				if (!reader.IsDBNull(26)) aidedEmployee.IsSingleParent = reader.GetByte(26);
				if (!reader.IsDBNull(27)) aidedEmployee.IncomingOfMonth = reader.GetDecimal(27);
				if (!reader.IsDBNull(28)) aidedEmployee.PersonNbrOfFamily = reader.GetByte(28);
				if (!reader.IsDBNull(29)) aidedEmployee.HUKOUSite = reader.GetString(29);
				if (!reader.IsDBNull(30)) aidedEmployee.IsMedicalInsurance = reader.GetByte(30);
				if (!reader.IsDBNull(31)) aidedEmployee.Resume = reader.GetString(31);
				if (!reader.IsDBNull(32)) aidedEmployee.Amout = reader.GetDecimal(32);
				if (!reader.IsDBNull(33)) aidedEmployee.ApplyingDate = reader.GetDateTime(33);

				return aidedEmployee;
			}
			else
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();

				return null;
			}
		}

		public void Insert(Core.Business.AidedEmployee aidedEmployee)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@EmployeeCode", SqlDbType.NVarChar, aidedEmployee.EmployeeCode);
			sql.AddParameter("@PoorLevel", SqlDbType.TinyInt, aidedEmployee.PoorLevel);
			sql.AddParameter("@Name", SqlDbType.NVarChar, aidedEmployee.Name);
			sql.AddParameter("@People", SqlDbType.NVarChar, aidedEmployee.People);
			sql.AddParameter("@Sex", SqlDbType.TinyInt, aidedEmployee.Sex);
			sql.AddParameter("@PolicticalExperience", SqlDbType.NVarChar, aidedEmployee.PolicticalExperience);
			sql.AddParameter("@Birthdya", SqlDbType.DateTime, aidedEmployee.Birthdya);
			sql.AddParameter("@Age", SqlDbType.TinyInt, aidedEmployee.Age);
			sql.AddParameter("@IDCardNbr", SqlDbType.NVarChar, aidedEmployee.IDCardNbr);
			sql.AddParameter("@HealthState", SqlDbType.TinyInt, aidedEmployee.HealthState);
			sql.AddParameter("@DisabilityLevel", SqlDbType.TinyInt, aidedEmployee.DisabilityLevel);
			sql.AddParameter("@Identity", SqlDbType.NVarChar, aidedEmployee.Identity);
			sql.AddParameter("@WorkingModelType", SqlDbType.NVarChar, aidedEmployee.WorkingModelType);
			sql.AddParameter("@HouseType", SqlDbType.NVarChar, aidedEmployee.HouseType);
			sql.AddParameter("@HouseArea", SqlDbType.Decimal, aidedEmployee.HouseArea);
			sql.AddParameter("@PostCode", SqlDbType.Int, aidedEmployee.PostCode);
			sql.AddParameter("@Phone", SqlDbType.NVarChar, aidedEmployee.Phone);
			sql.AddParameter("@YearOfWorking", SqlDbType.TinyInt, aidedEmployee.YearOfWorking);
			sql.AddParameter("@Industry", SqlDbType.NVarChar, aidedEmployee.Industry);
			sql.AddParameter("@IsMarried", SqlDbType.TinyInt, aidedEmployee.IsMarried);
			sql.AddParameter("@HUKOUtype", SqlDbType.NVarChar, aidedEmployee.HUKOUtype);
			sql.AddParameter("@Address", SqlDbType.NVarChar, aidedEmployee.Address);
			sql.AddParameter("@Organization", SqlDbType.NVarChar, aidedEmployee.Organization);
			sql.AddParameter("@OZType", SqlDbType.NVarChar, aidedEmployee.OZType);
			sql.AddParameter("@OZState", SqlDbType.NVarChar, aidedEmployee.OZState);
			sql.AddParameter("@IsSingleParent", SqlDbType.TinyInt, aidedEmployee.IsSingleParent);
			sql.AddParameter("@IncomingOfMonth", SqlDbType.Decimal, aidedEmployee.IncomingOfMonth);
			sql.AddParameter("@PersonNbrOfFamily", SqlDbType.TinyInt, aidedEmployee.PersonNbrOfFamily);
			sql.AddParameter("@HUKOUSite", SqlDbType.NVarChar, aidedEmployee.HUKOUSite);
			sql.AddParameter("@IsMedicalInsurance", SqlDbType.TinyInt, aidedEmployee.IsMedicalInsurance);
			sql.AddParameter("@Resume", SqlDbType.NVarChar, aidedEmployee.Resume);
			sql.AddParameter("@Amout", SqlDbType.Decimal, aidedEmployee.Amout);
			sql.AddParameter("@ApplyingDate", SqlDbType.DateTime, aidedEmployee.ApplyingDate);
			SqlDataReader reader = sql.ExecuteSqlReader(SqlInsertAidedEmployee);

			if (reader != null && !reader.IsClosed && reader.Read())
			{
				if (!reader.IsDBNull(0)) aidedEmployee.Id = int.Parse(reader.GetDecimal(0).ToString());

				reader.Close();
			}
			else
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void Update(Core.Business.AidedEmployee aidedEmployee)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@EmployeeCode", SqlDbType.NVarChar, aidedEmployee.EmployeeCode);
			sql.AddParameter("@PoorLevel", SqlDbType.TinyInt, aidedEmployee.PoorLevel);
			sql.AddParameter("@Name", SqlDbType.NVarChar, aidedEmployee.Name);
			sql.AddParameter("@People", SqlDbType.NVarChar, aidedEmployee.People);
			sql.AddParameter("@Sex", SqlDbType.TinyInt, aidedEmployee.Sex);
			sql.AddParameter("@PolicticalExperience", SqlDbType.NVarChar, aidedEmployee.PolicticalExperience);
			sql.AddParameter("@Birthdya", SqlDbType.DateTime, aidedEmployee.Birthdya);
			sql.AddParameter("@Age", SqlDbType.TinyInt, aidedEmployee.Age);
			sql.AddParameter("@IDCardNbr", SqlDbType.NVarChar, aidedEmployee.IDCardNbr);
			sql.AddParameter("@HealthState", SqlDbType.TinyInt, aidedEmployee.HealthState);
			sql.AddParameter("@DisabilityLevel", SqlDbType.TinyInt, aidedEmployee.DisabilityLevel);
			sql.AddParameter("@Identity", SqlDbType.NVarChar, aidedEmployee.Identity);
			sql.AddParameter("@WorkingModelType", SqlDbType.NVarChar, aidedEmployee.WorkingModelType);
			sql.AddParameter("@HouseType", SqlDbType.NVarChar, aidedEmployee.HouseType);
			sql.AddParameter("@HouseArea", SqlDbType.Decimal, aidedEmployee.HouseArea);
			sql.AddParameter("@PostCode", SqlDbType.Int, aidedEmployee.PostCode);
			sql.AddParameter("@Phone", SqlDbType.NVarChar, aidedEmployee.Phone);
			sql.AddParameter("@YearOfWorking", SqlDbType.TinyInt, aidedEmployee.YearOfWorking);
			sql.AddParameter("@Industry", SqlDbType.NVarChar, aidedEmployee.Industry);
			sql.AddParameter("@IsMarried", SqlDbType.TinyInt, aidedEmployee.IsMarried);
			sql.AddParameter("@HUKOUtype", SqlDbType.NVarChar, aidedEmployee.HUKOUtype);
			sql.AddParameter("@Address", SqlDbType.NVarChar, aidedEmployee.Address);
			sql.AddParameter("@Organization", SqlDbType.NVarChar, aidedEmployee.Organization);
			sql.AddParameter("@OZType", SqlDbType.NVarChar, aidedEmployee.OZType);
			sql.AddParameter("@OZState", SqlDbType.NVarChar, aidedEmployee.OZState);
			sql.AddParameter("@IsSingleParent", SqlDbType.TinyInt, aidedEmployee.IsSingleParent);
			sql.AddParameter("@IncomingOfMonth", SqlDbType.Decimal, aidedEmployee.IncomingOfMonth);
			sql.AddParameter("@PersonNbrOfFamily", SqlDbType.TinyInt, aidedEmployee.PersonNbrOfFamily);
			sql.AddParameter("@HUKOUSite", SqlDbType.NVarChar, aidedEmployee.HUKOUSite);
			sql.AddParameter("@IsMedicalInsurance", SqlDbType.TinyInt, aidedEmployee.IsMedicalInsurance);
			sql.AddParameter("@Resume", SqlDbType.NVarChar, aidedEmployee.Resume);
			sql.AddParameter("@Amout", SqlDbType.Decimal, aidedEmployee.Amout);
			sql.AddParameter("@ApplyingDate", SqlDbType.DateTime, aidedEmployee.ApplyingDate);
			sql.AddParameter("@Id", SqlDbType.Int, aidedEmployee.Id);
			sql.ExecuteSql(SqlUpdateAidedEmployee);
		}

		public void Delete(Core.Business.AidedEmployee aidedEmployee)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Id", SqlDbType.Int, aidedEmployee.Id);
			sql.ExecuteSql(SqlDeleteAidedEmployee);
		}

		public IList<Core.Business.AidedEmployee> GetAllAidedEmployee()
		{
			IList<Core.Business.AidedEmployee> aidedEmployeelist = new List<Core.Business.AidedEmployee>();
			SqlServerUtility sql = new SqlServerUtility();

			SqlDataReader reader = sql.ExecuteSqlReader(SqlGetAllAidedEmployee);

			if(reader != null)
			{
				while(reader.Read())
				{
					Core.Business.AidedEmployee aidedEmployee = new Core.Business.AidedEmployee();

					if (!reader.IsDBNull(0)) aidedEmployee.Id = reader.GetInt32(0);
					if (!reader.IsDBNull(1)) aidedEmployee.EmployeeCode = reader.GetString(1);
					if (!reader.IsDBNull(2)) aidedEmployee.PoorLevel = reader.GetByte(2);
					if (!reader.IsDBNull(3)) aidedEmployee.Name = reader.GetString(3);
					if (!reader.IsDBNull(4)) aidedEmployee.People = reader.GetString(4);
					if (!reader.IsDBNull(5)) aidedEmployee.Sex = reader.GetByte(5);
					if (!reader.IsDBNull(6)) aidedEmployee.PolicticalExperience = reader.GetString(6);
					if (!reader.IsDBNull(7)) aidedEmployee.Birthdya = reader.GetDateTime(7);
					if (!reader.IsDBNull(8)) aidedEmployee.Age = reader.GetByte(8);
					if (!reader.IsDBNull(9)) aidedEmployee.IDCardNbr = reader.GetString(9);
					if (!reader.IsDBNull(10)) aidedEmployee.HealthState = reader.GetByte(10);
					if (!reader.IsDBNull(11)) aidedEmployee.DisabilityLevel = reader.GetByte(11);
					if (!reader.IsDBNull(12)) aidedEmployee.Identity = reader.GetString(12);
					if (!reader.IsDBNull(13)) aidedEmployee.WorkingModelType = reader.GetString(13);
					if (!reader.IsDBNull(14)) aidedEmployee.HouseType = reader.GetString(14);
					if (!reader.IsDBNull(15)) aidedEmployee.HouseArea = reader.GetDecimal(15);
					if (!reader.IsDBNull(16)) aidedEmployee.PostCode = reader.GetInt32(16);
					if (!reader.IsDBNull(17)) aidedEmployee.Phone = reader.GetString(17);
					if (!reader.IsDBNull(18)) aidedEmployee.YearOfWorking = reader.GetByte(18);
					if (!reader.IsDBNull(19)) aidedEmployee.Industry = reader.GetString(19);
					if (!reader.IsDBNull(20)) aidedEmployee.IsMarried = reader.GetByte(20);
					if (!reader.IsDBNull(21)) aidedEmployee.HUKOUtype = reader.GetString(21);
					if (!reader.IsDBNull(22)) aidedEmployee.Address = reader.GetString(22);
					if (!reader.IsDBNull(23)) aidedEmployee.Organization = reader.GetString(23);
					if (!reader.IsDBNull(24)) aidedEmployee.OZType = reader.GetString(24);
					if (!reader.IsDBNull(25)) aidedEmployee.OZState = reader.GetString(25);
					if (!reader.IsDBNull(26)) aidedEmployee.IsSingleParent = reader.GetByte(26);
					if (!reader.IsDBNull(27)) aidedEmployee.IncomingOfMonth = reader.GetDecimal(27);
					if (!reader.IsDBNull(28)) aidedEmployee.PersonNbrOfFamily = reader.GetByte(28);
					if (!reader.IsDBNull(29)) aidedEmployee.HUKOUSite = reader.GetString(29);
					if (!reader.IsDBNull(30)) aidedEmployee.IsMedicalInsurance = reader.GetByte(30);
					if (!reader.IsDBNull(31)) aidedEmployee.Resume = reader.GetString(31);
					if (!reader.IsDBNull(32)) aidedEmployee.Amout = reader.GetDecimal(32);
					if (!reader.IsDBNull(33)) aidedEmployee.ApplyingDate = reader.GetDateTime(33);

					aidedEmployee.MarkOld();
					aidedEmployeelist.Add(aidedEmployee);
				}
				reader.Close();
			}
			return aidedEmployeelist;
		}
		#endregion
	}
}
